<?php
	class DBUtil {
		private $con;
	
		function DBUtil() {
			$this -> connectToDatabase();
		}
		
		public function checkCredentials($username, $password) {
			$sqlQuery = "SELECT u.personalnummer, u.password FROM t_user u WHERE u.personalnummer = '".$username."' AND u.password = '".$password."'";
			$result = mysqli_query($this -> con, $sqlQuery);

			if(mysqli_num_rows($result) == 1) {
				return true;
			}
			return false;
		}
	
		public function getAllBuildings() {
			$sqlQuery = "SELECT b.id, b.name, b.power_consumption, b.status, b.unit, (SELECT COUNT(*) FROM t_room r WHERE r.building_id = b.id) AS room_count, (SELECT COUNT(*) FROM t_plug p WHERE p.building_id = b.id) AS plug_count FROM t_building b";
			return $this -> executeSQLQueryAndFetchResultArray($sqlQuery);
		}
		
		public function getBuilding($buildingId) {
			$buildingId = mysqli_real_escape_string($this -> con, $buildingId);
			$sqlQuery = "SELECT b.id, b.name, b.power_consumption, b.status, b.unit, (SELECT COUNT(*) FROM t_room r WHERE r.building_id = b.id) AS room_count, (SELECT COUNT(*) FROM t_plug p WHERE p.building_id = b.id) AS plug_count FROM t_building b WHERE b.id = '" . $buildingId . "'";
			return $this -> executeSQLQueryAndFetchResultArray($sqlQuery);
		}
		
		public function getRooms($buildingId = 0) {
			if($buildingId == 0) {
				$sqlQuery = "SELECT r.id, r.name, r.power_consumption, r.status, r.unit, r.building_id, (SELECT COUNT(*) FROM t_plug p WHERE p.room_id = r.id) AS plug_count, b.name AS building_name FROM t_room r, t_building b WHERE b.id = r.building_id";
				return $this -> executeSQLQueryAndFetchResultArray($sqlQuery);
			}
			else {
				return $this -> getAllRoomsInBuilding($buildingId);
			}
		}
		
		private function getAllRoomsInBuilding($buildingId) {
			$buildingId = mysqli_real_escape_string($this -> con, $buildingId);
			$sqlQuery = "SELECT r.id, r.name, r.power_consumption, r.status, r.unit, r.building_id, (SELECT COUNT(*) FROM t_plug p WHERE p.room_id = r.id) AS plug_count, b.name AS building_name FROM t_room r, t_building b WHERE r.building_id = '" . $buildingId . "' AND b.id = r.building_id";
			return $this -> executeSQLQueryAndFetchResultArray($sqlQuery);
		}
		
		public function getRoom($roomId) {
			$buildingId = mysqli_real_escape_string($this -> con, $buildingId);
			$sqlQuery = "SELECT r.id, r.name,r.power_consumption, r.status, r.unit, r.building_id, (SELECT COUNT(*) FROM t_plug p WHERE p.room_id = r.id) AS plug_count, b.name AS building_name, power_consumption, status, unit FROM t_room r, t_building b WHERE r.id = '" . $roomId . "' AND b.id = r.building_id";
			return $this -> executeSQLQueryAndFetchResultArray($sqlQuery);
		}
		
		public function getPlugs($roomId = 0) {
			if($roomId == 0) {
				$sqlQuery = "SELECT p.id, p.name, p.power_consumption, p.status, p.unit, r.name AS room_name, b.name AS building_name FROM t_plug p, t_room r, t_building b WHERE r.id = p.room_id AND b.id = p.building_id";
				return $this -> executeSQLQueryAndFetchResultArray($sqlQuery);
			}
			else {
				return $this -> getAllPlugsInRoom($roomId);
			}
		}
		
		private function getAllPlugsInRoom($roomId) {
			$roomId = mysqli_real_escape_string($this -> con, $roomId);
			$sqlQuery = "SELECT p.id, p.name, p.power_consumption, p.status, p.unit, r.name AS room_name, b.name AS building_name FROM t_plug p, t_room r, t_building b WHERE p.room_id = '" . $roomId . "' AND r.id = p.room_id AND b.id = p.building_id";
			return $this -> executeSQLQueryAndFetchResultArray($sqlQuery);
		}
		
		public function getPlug($plugId) {
			$plugId = mysqli_real_escape_string($this -> con, $plugId);
			$sqlQuery = "SELECT p.id, p.name, p.power_consumption, p.status, p.unit, r.name AS room_name, b.name AS building_name, power_consumption, status, unit FROM t_plug p, t_room r, t_building b WHERE p.id = '" . $plugId . "' r.id = p.room_id AND b.id = p.building_id";
			return $this -> executeSQLQueryAndFetchResultArray($sqlQuery);
		}
		
		private function executeSQLQueryAndFetchResultArray($sqlQuery) {
			$result = mysqli_query($this -> con, $sqlQuery);
			$numberOfResults = mysqli_num_rows($result);
				
			for($i = 0; $i < $numberOfResults; $i++) {
				$resultArray[$i] = mysqli_fetch_assoc($result);
			}
				
			return $resultArray;
		}
		
		private function connectToDatabase() {
			$dbHost = "localhost";
			$dbUser = "root";
			$dbPass = "";
			$dbName = "lightcontrol";
			
			$this -> con = mysqli_connect($dbHost, $dbUser, $dbPass) or die ("Es konnte keine Verbindung zur Datenbank hergestellt werden.");
			mysqli_select_db($this -> con, $dbName) or die("Die Datenbank konnte nicht selektiert werden.");
		}
	}
?>